{% extends 'basic_app/master.html' %}
{% block tag_left %}
    {% include 'oratk_app/tag_left_user.html' %}
{% endblock %}
{% block title %}
    <title>ORATK</title>
{% endblock %}
{% block userinfo %}
    <div class="col-sm-9 col-sm-offset-3 col-md-10 col-md-offset-2 main">
        <h2 class="page-header">ORATK 自定义查询</h2>
        <form class="form-horizontal" action="{{ request.path }}" onsubmit="return validateForm()" method="post"
              target="_blank">
            <div class="col-md-12">
                <div class="panel panel-default">
                    <div class="panel-heading form-inline">
                        <div>&nbsp 选择实例: &nbsp
                            <select class="selectpicker show-tick" data-live-search="true" name="conn_string"
                                    id="conn_string">
                                <option id="def">--请选择实例名--</option>
                                <optgroup label="total:{{ instanceinfo_result_total }}">
                                    {% for i in instanceinfo_result %}
                                        <option
                                                data-content="
                <div>{{ i.id }}.{{ i.dbname }}</div>
                <div style='padding-left: 15px;'><small>{{ i.conn_string }}</small></div>
            "
                                                value="{{ i.id }}.{{ i.dbname }}={{ i.conn_string }}">
                                        </option>
                                    {% endfor %}
                                </optgroup>
                            </select>
                            &nbsp 选择Schema: &nbsp
                            <select class="selectpicker show-tick" data-live-search="true" name="selectSchema"
                                    id="selectSchema">
                                <option id="def">--请选择Schema名--</option>
                            </select>
                        </div>
                        &nbsp
                        <div> &nbsp 下方输入要查询的语句: &nbsp</div>
                    </div>
                    <div class="panel-body">
                        <div class="col-md-9 column">
                            <textarea type="text" class="form-control" name="sqltext" id="sqltext"
                                      style="height: 300px; resize: none"></textarea>
                        </div>
                        <div class="col-md-3  column">
                            <div style="margin: 6px"> &nbsp
                            </div>
                            <div>
                                <button type="button" class="btn btn-info" id="btSearch">查询(本页展示）</button>
                            </div>
                            &nbsp
                            <div>
                                <button type="submit" class="btn btn-info" id="btSearch_new">查询(新页展示）</button>
                            </div>
                            &nbsp
                            <div>
                                <button type="button" class="btn btn-info" id="btsave_sql">保存sql</button>
                            </div>
                            &nbsp
                            <div class="text-info">
                                <li>
                                    仅支持select 语句
                                </li>
                                <li>
                                    仅支持单条语句
                                </li>
                                <li>
                                    语句开始和结尾不能有注释
                                </li>
                                <li>
                                    查询结果不能超过500
                                </li>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </form>
        <div class="col-md-12">
            <div class="panel panel-default">
                <div class="panel-heading form-inline">
                    <div> &nbsp 查询结果: &nbsp</div>
                </div>
                <div class="panel-body">
                    <div class="col-md-12 column">
                        <div id="tab_detail" class="col-md-12"></div>
                    </div>
                </div>
            </div>
        </div>

        <div class="col-md-12">
            <div class="panel panel-default">
                <div class="panel-heading form-inline">
                    <div> &nbsp sql记录: &nbsp</div>
                </div>
                <div class="panel-body">
                    <div class="col-md-12 column" id="table_pagination">
                        {% include 'basic_app/search_ajax.html' %}
                        <table class="table table-striped table-bordered table-hover">
                            <thead>
                            <tr>
                                {#                    <th>+</th>#}
                                <th>名称</th>
                                <th>SQLTEXT</th>
                                <th>备注</th>
                                <th>创建时间</th>
                                <th>操作</th>
                            </tr>
                            </thead>
                            <tbody id="tabbody">
                            {% for i in current_page %}
                                <tr>
                                    {#                        <td><input type="checkbox"/></td>#}
                                    <td style="display:none">{{ i.id }}</td>
                                    <td>{{ i.name }}</td>
                                    <td>{{ i.sqltext }}</td>
                                    <td>{{ i.remark | default_if_none:"" }}</td>
                                    <td>{{ i.crtime }}</td>
                                    <td>
                                        <a style="cursor:pointer"
                                           onclick="if(confirm('确认删除吗？')==false)return false; else oratk_query_savesql_delete({{ i.id }})">删除</a>
                                    </td>
                                </tr>
                            {% endfor %}


                            </tbody>
                        </table>
                        {#分页#}
                        {% include 'basic_app/paginator_common_ajax.html' %}

                    </div>
                </div>
            </div>
        </div>

    </div>

    {#    保存sql的模态框#}
    <div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
        <div class="modal-dialog modal-dialog" id="docModalContent">
            <div class="modal-content">
                <div class="modal-header">
                    <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
                    <h4 class="modal-title" id="myModalLabel">保存sql</h4>
                </div>
                <span>&nbsp</span>
                <form class="form-horizontal" action="/basic_app/oratk_query_savesql_ajax" method="post"
                      style="padding-left: 10%" id="editForm">
                    <div class="form-group">
                        <label class="col-sm-2 control-label">名称：</label>
                        <div class="col-sm-10">
                            <input type="text" style="width:400px;" class="form-control" name="save_sql_name"
                                   id="save_sql_name"
                                   placeholder="名称">
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="col-sm-2 control-label">sqltext：</label>
                        <div class="col-sm-10">
                            <textarea id="save_sql" style="width:400px;height:200px" type="text_area"
                                      class="form-control"
                                      name="save_sql" placeholder="sqltext"></textarea>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
                        <button type="button" class="btn btn-primary" id="modal_save_sql">提交</button>
                    </div>
                </form>
            </div><!-- /.modal-content -->
        </div><!-- /.modal -->
    </div>

{% endblock %}

{% block script %}
    <script>
        //新页展示
        function validateForm() {
            var select_conn = $('#conn_string').find("option:checked").attr("id")
            var sqltext_value = document.getElementById("sqltext").value
            sqltext = sqltext_value.replace(/\s*/g, "")
            check_select = sqltext.substring(0, 6).toUpperCase()
            if (select_conn == 'def') {
                alert('请选择实例')
                return false;
            } else if (sqltext == '') {
                alert('请输入查询sql')
                return false;
            }
            //校验是否为select语句
            else if (check_select != 'SELECT') {
                alert('请输入正确的sql')
                return false;
            }
        }

        //本页展示
        $('#btSearch').click(function () {
            var select_conn = $('#conn_string').find("option:checked").attr("id")
            var sqltext_value = document.getElementById("sqltext").value
            console.log(sqltext_value)
            sqltext = sqltext_value.replace(/(^\s*)/g, "");
            {#check_select = sqltext.substring(0, 6).toUpperCase()#}
            check_select = sqltext.split(' ')
            console.log(check_select)
            check_fenhao = sqltext.substr(sqltext.length - 1, 1)

            if (select_conn == 'def') {
                alert('请选择实例')
                return false;
            } else if (sqltext == '') {
                alert('请输入查询sql')
                return false;
            }
            //校验是否为select语句
            else if (check_select[0].toUpperCase() != 'SELECT') {
                alert('请输入正确的sql')
                return false;
            } else {
                //如果分号结尾，把分号去掉
                {#if (check_fenhao == ';') {#}
                {#    sqltext_value = sqltext_value.replace(/\s*$/g, '')#}
                {#    sqltext_value = sqltext_value.substr(0, sqltext_value.length - 1)}#}
                $.ajax({
                    type: "POST",
                    data: {
                        'conn_string': $('#conn_string').val(),
                        sqltext: sqltext_value,
                        'selectSchema': $('#selectSchema').val(),
                        'submit_id': 2
                    },
                    url: "/oratk_app/oratk_query", //后台处理函数的url
                    cache: false,
                    dataType: "html",
                    beforeSend: function () {
                        // $('#loadingModal').modal({backdrop: 'static', keyboard: false});
                        showLoading()
                        $('#btSearch').button('loading')
                    },
                    complete: function () {
                        //$('#loadingModal').modal('hide');
                        hideLoading()
                        $('#btSearch').button('reset')
                    },
                    success: function (result) {
                        $("#tab_detail").html(result);
                    },
                    error: function (XMLHttpRequest, textStatus, errorThrown) {
                        alert(XMLHttpRequest.responseText);
                    },
                })
            }


        })
        $('#btSearch_new').click(function () {
            var select_conn = $('#conn_string').find("option:checked").attr("id")
            var select_conn_id = $('#conn_string').find("option:checked").attr("u_info")
            var sqltext_value = document.getElementById("sqltext").value
            {#sqltext = sqltext_value.replace(/\s*/g, "")#}
            console.log(sqltext_value)
            sqltext = sqltext_value.replace(/(^\s*)/g, "");
            {#check_select = sqltext.substring(0, 6).toUpperCase()#}
            check_select = sqltext.split(' ')
            console.log(check_select)
            check_fenhao = sqltext.substr(sqltext.length - 1, 1)

            if (select_conn == 'def') {
                alert('请选择实例')
                return false;
            } else if (sqltext == '') {
                alert('请输入查询sql')
                return false;
            }
            //校验是否为select语句
            else if (check_select[0].toUpperCase() != 'SELECT') {
                alert('请输入正确的sql')
                return false;
            } else {
                //如果分号结尾，把分号去掉
                {#if (check_fenhao == ';') {#}
                {#    sqltext_value = sqltext_value.replace(/\s*$/g, '')#}
                {#    sqltext_value = sqltext_value.substr(0, sqltext_value.length - 1)}#}
                $.ajax({
                    type: "POST",
                    data: {
                        'conn_string': $('#conn_string').val(),
                        {#'sqltext': document.getElementById("sqltext").value,#}
                        sqltext: sqltext_value,
                        'selectSchema': $('#selectSchema').val(),
                        'conn_id': select_conn_id,
                        'submit_id': 2
                    },
                    url: "/oratk_app/oratk_query", //后台处理函数的url
                    cache: false,
                    dataType: "html",
                    beforeSend: function () {
                        // $('#loadingModal').modal({backdrop: 'static', keyboard: false});
                        showLoading()
                        $('#btSearch_new').button('loading')
                    },
                    complete: function () {
                        //$('#loadingModal').modal('hide');
                        hideLoading()
                        $('#btSearch_new').button('reset')
                    },
                    success: function (result) {
                        $("#tab_detail").html(result);
                    },
                    error: function (XMLHttpRequest, textStatus, errorThrown) {
                        alert(XMLHttpRequest.responseText);
                    },
                })
            }


        })

        //table 显示行号
        function indexFormatter(value, row, index) {
            return index + 1;
        }


        // 根据不同实例来展示Schema
        $('#conn_string').change(function () {
            var conn_string_check = $('#conn_string').find("option:checked").attr("id");
            if (conn_string_check !== 'def') {
                // $("#file_sheet_div").empty()
                $.ajax({
                    type: "POST",
                    data: {
                        'conn_string': $('#conn_string').val()
                    },
                    url: "/oratk_app/oratk_query_selectSchema_ajax", //后台处理函数的url
                    cache: false,
                    dataType: "html",
                    beforeSend: function () {
                        // $('#loadingModal').modal({backdrop: 'static', keyboard: false});
                        showLoading()
                        //$('#submit_id').button('loading')
                    },
                    complete: function () {
                        //$('#loadingModal').modal('hide');
                        hideLoading()
                        //$('#submit_id').button('reset')
                    },
                    success: function (result) {
                        {#console.log('selectSchema-->success')#}
                        {#console.log(result)#}
                        {#$("#selectSchema").html(result);#}
                        $("#selectSchema").append().html(result)
                        //使用refresh方法更新UI以匹配新状态。
                        $('#selectSchema').selectpicker('refresh');
                        //render方法强制重新渲染引导程序 - 选择ui。
                        $('#selectSchema').selectpicker('render');

                        //console.log(result)
                    },
                    error: function (XMLHttpRequest, textStatus, errorThrown) {
                        showconnectionAlert(XMLHttpRequest, textStatus, errorThrown);
                    },
                })
            } else {
                $('#selectSchema').empty()
                $("#selectSchema").append('<option id="def">--请选择Schema名--</option>')
                //使用refresh方法更新UI以匹配新状态。
                $('#selectSchema').selectpicker('refresh');
                //render方法强制重新渲染引导程序 - 选择ui。
                $('#selectSchema').selectpicker('render');
            }
        })

        //保存sql时调用模态框
        $('#btsave_sql').click(function () {
            var sqltext_value = document.getElementById("sqltext").value
            {#sqltext = sqltext_value.replace(/\s*/g, "")#}
            console.log(sqltext_value)
            sqltext = sqltext_value.replace(/(^\s*)/g, "");
            {#check_select = sqltext.substring(0, 6).toUpperCase()#}
            check_select = sqltext.split(' ')
            console.log(check_select)
            check_fenhao = sqltext.substr(sqltext.length - 1, 1)

            //校验是否为select语句
            if (check_select[0].toUpperCase() != 'SELECT') {
                alert('请输入正确的sql')
                return false;
            } else {
                $('#save_sql').val(sqltext_value);
                $('#save_sql').attr("disabled", "disabled");

                //调用模态框
                $('#myModal').modal('show')
            }
        })

        //1、保存sql-->模态框 提交
        $('#modal_save_sql').click(function () {
            var sqltext_value = document.getElementById("sqltext").value
            sqltext = sqltext_value.replace(/(^\s*)/g, "");
            var save_sql_name = document.getElementById("save_sql_name").value

            {#var line_count_set = $('.line_count').val();#}
            {#var search = $('.aa_search').val();#}
            {#var page_num = $('#aa_select_page_num').text();#}
            if (save_sql_name == '') {
                alert('请输入名称')
                return false;
            } else {

                $.ajax({
                    type: "POST",
                    data: {
                        'save_sql_name': $('#save_sql_name').val(),
                        sqltext: sqltext,
                        save_sql_tag: 'YES',
                        {#line_count: line_count_set,#}
                        {#user_search: search,#}
                        {#page: page_num#}
                    },
                    url: "/oratk_app/oratk_query_savesql_delete_ajax", //后台处理函数的url
                    cache: false,
                    dataType: "html",
                    beforeSend: function () {
                        // $('#loadingModal').modal({backdrop: 'static', keyboard: false});
                        showLoading()
                        $('#modal_save_sql').button('loading')
                    },
                    complete: function () {
                        //$('#loadingModal').modal('hide');
                        hideLoading()
                        $('#modal_save_sql').button('reset')
                    },
                    success: function (result) {
                        $('#myModal').modal('hide');
                        $("#table_pagination").html(result);
                    },
                    error: function (XMLHttpRequest, textStatus, errorThrown) {
                        alert(XMLHttpRequest.responseText);
                    },
                })
            }

        })

        //删除savesql
        function oratk_query_savesql_delete(id) {
            console.log('save_sql_delete')
            var url = "/oratk_app/oratk_query_savesql_delete_ajax?nid=" + id;
            var nid = id;
            $.ajax({
                type: "POST",
                data: {
                    'nid': nid,
                    'delete_tag': 'YES'
                },
                url: "/oratk_app/oratk_query_savesql_delete_ajax", //后台处理函数的url
                cache: false,
                dataType: "html",
                success: function (result) {
                    $("#table_pagination").html(result);
                }
            })
        }
    </script>
{% endblock %}